package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sg.hms.Dao.RoomDao;
import com.sg.hms.Model.Equiptment;
import com.sg.hms.Model.Pagebean;
import com.sg.hms.Model.Room;
import com.sg.hms.Util.JDBCUtil;
/***
 * 操作房间信息方法的具体实现
 * @author 吴磊
 *
 */
public class RoomDaoImpl implements RoomDao{

	@Override
	public ArrayList<Room> getRoom() {
		// TODO Auto-generated method stub
		ArrayList<Room> rooms=new ArrayList<Room>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select room.rId,room.rName,equipment.eName,equipment_room.count,equipment_room.state from room,equipment_room,equipment where room.rId=equipment_room.rId and equipment_room.eId=equipment.eId");
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sql.toString());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				Room room=new Room(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5));
				rooms.add(room);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rooms;
	}

	@Override
	public ArrayList<Room> getRoom(Pagebean pagebean) {
		// TODO Auto-generated method stub
		ArrayList<Room> rooms=new ArrayList<Room>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select room.rId,room.rName,equipment.eName,equipment_room.count,equipment_room.state,room.elId,equipment_room.eId from room,equipment_room,equipment where room.rId=equipment_room.rId and equipment_room.eId=equipment.eId limit ?,?");
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sql.toString());
			ps.setInt(1, pagebean.getStart());
			ps.setInt(2, pagebean.getPageSize());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				Room room=new Room(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getInt(6),rs.getInt(7));
				rooms.add(room);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rooms;
	}

	@Override
	public void DeleteRoom(String[] a, String[] b) {
		// TODO Auto-generated method stub
		Connection con=JDBCUtil.getConnection();
		PreparedStatement ps=null;
	    for (int i = 0; i < b.length; i++) {
			StringBuffer sql=new StringBuffer("delete from equipment_room where rId=? and eId=?");
			try {
				ps=con.prepareStatement(sql.toString());
				ps.setInt(1,Integer.parseInt(a[i]));
				ps.setInt(2, Integer.parseInt(b[i]));
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public ArrayList<Room> getRoom(String key) {
		// TODO Auto-generated method stub
		ArrayList<Room> rooms=new ArrayList<Room>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select room.rId,room.rName,equipment.eName,equipment_room.count,equipment_room.state from room,equipment_room,equipment where room.rId=equipment_room.rId and equipment_room.eId=equipment.eId and room.rName like ? or equipment.eName like ?");
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sql.toString());
			ps.setString(1, "%"+key+"%");
			ps.setString(2, "%"+key+"%");
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				Room room=new Room(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5));
				rooms.add(room);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rooms;
	}

	@Override
	public ArrayList<Room> getRoom(Pagebean pagebean, String key) {
		// TODO Auto-generated method stub
		ArrayList<Room> rooms=new ArrayList<Room>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select room.rId,room.rName,equipment.eName,equipment_room.count,equipment_room.state,room.elId,equipment_room.eId from room,equipment_room,equipment where room.rId=equipment_room.rId and equipment_room.eId=equipment.eId and room.rName like ? or equipment.eName like ? limit ?,?");
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sql.toString());
			ps.setString(1, "%"+key+"%");
			ps.setString(2, "%"+key+"%");
			ps.setInt(3, pagebean.getStart());
			ps.setInt(4, pagebean.getPageSize());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				Room room=new Room(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getInt(6),rs.getInt(7));
				rooms.add(room);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rooms;
	}

	@Override
	public void updateRoom(int rId, int eId, int count, String state) {
		// TODO Auto-generated method stub
		Connection con=JDBCUtil.getConnection();
		String sql="update equipment_room set count=?,state=? where rId=? and eId=? ";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, count);
			ps.setString(2, state);
			ps.setInt(3, rId);
			ps.setInt(4, eId);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public String getString(int rId, int eId) {
		// TODO Auto-generated method stub
		String string="";
		Connection con=JDBCUtil.getConnection();
		String sql="select count,state from equipment_room where rId=? and eId=? ";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, rId);
			ps.setInt(2, eId);
			ResultSet rs=ps.executeQuery();
			if (rs.next()) {
				string+="数量："+rs.getInt(1)+"状态："+rs.getString(2);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return string;
	}

	@Override
	public int getRidByRname(String rName) {
		// TODO Auto-generated method stub
		int temp=0;
		Connection con=JDBCUtil.getConnection();
		String sql="select rId from room where rName=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, rName);
			ResultSet rs=ps.executeQuery();
			if (rs.next()) {
				temp=rs.getInt(1);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int getEidByEid(String eName) {
		// TODO Auto-generated method stub
		int temp=0;
		Connection con=JDBCUtil.getConnection();
		String sql="select eId from equipment where eName=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, eName);
			ResultSet rs=ps.executeQuery();
			if (rs.next()) {
				temp=rs.getInt(1);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int exixt(int rId, int eId) {
		// TODO Auto-generated method stub
		int temp=0;
		Connection con=JDBCUtil.getConnection();
		String sql="select count(rId) from equipment_room where rId=? and eId=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, rId);
			ps.setInt(2, eId);
			ResultSet rs=ps.executeQuery();
			if (rs.next()) {
				temp=rs.getInt(1);
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int addRoom(int rId, int eId, int count, String state) {
		// TODO Auto-generated method stub
		int temp=0;
		Connection con=JDBCUtil.getConnection();
		String sql="insert into equipment_room (eId,rId,count,state) values(?,?,?,?)";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, eId);
			ps.setInt(2, rId);
			ps.setInt(3, count);
			ps.setString(4, state);
			temp=ps.executeUpdate();
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public ArrayList<Equiptment> gEquiptments() {
		// TODO Auto-generated method stub
		ArrayList<Equiptment> list=new ArrayList<Equiptment>();
		Connection con=JDBCUtil.getConnection();
		String sql="select eId,sum(count) from equipment_room group by eId";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Equiptment(rs.getInt(1), rs.getInt(2)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Equiptment> getAllEquiptment() {
		// TODO Auto-generated method stub
		ArrayList<Equiptment> list=new ArrayList<Equiptment>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from equipment";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Equiptment(rs.getInt(1), rs.getString(3),rs.getInt(4)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int updateEquiptmentNum(int eId, int num) {
		// TODO Auto-generated method stub
		RoomDaoImpl daoImpl=new RoomDaoImpl();
		int count=0;
		int num1=daoImpl.getcurrentEquiptmentNum(eId);
		Connection con=JDBCUtil.getConnection();
		String sql="update equipment set sum=? where eId=?";
		int num2=num1+num;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, num2);
			ps.setInt(2, eId);
			count=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int getcurrentEquiptmentNum(int eId) {
		// TODO Auto-generated method stub
		int num=0;
		Connection con=JDBCUtil.getConnection();
		String sql="select sum from equipment where eId=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, eId);
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				num=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
	}

}
